using Dapper;
using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;
using System.Text;
using System.Diagnostics;

namespace Lzfy_His_Service.Repositories
{
    /// <summary>
    /// 退费情况查询仓储实现
    /// </summary>
    public class RefundStatusRepository : IRefundStatusRepository
    {
        private readonly IOracleConnectionFactory _oracleConnectionFactory;
        private readonly ILogger<RefundStatusRepository> _logger;
        private readonly ConnectionLeakDetector _leakDetector;

        public RefundStatusRepository(IOracleConnectionFactory oracleConnectionFactory, ILogger<RefundStatusRepository> logger, ConnectionLeakDetector leakDetector)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
            _logger = logger;
            _leakDetector = leakDetector;
        }

        /// <summary>
        /// 分页查询退费情况数据（优化版本，使用id字段进行高效分页）
        /// </summary>
        public async Task<(IEnumerable<RefundStatusData> data, int totalCount)> GetPagedAsync(
            int pageIndex, int pageSize, string? name = null, string? idNumber = null, 
            string? refundResult = null, DateTime? startDate = null, DateTime? endDate = null)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var whereClause = BuildWhereClause(name, idNumber, refundResult, startDate, endDate);
                var parameters = BuildParameters(name, idNumber, refundResult, startDate, endDate);
                
                // 获取总记录数（仅在第一页时查询，减少不必要的COUNT查询）
                int totalCount = 0;
                if (pageIndex == 0)
                {
                    var countSql = $@"
                        SELECT COUNT(1) 
                        FROM pca.lzfy_prepay_refund_info t1
                        JOIN pca.pca_prepay_account_index t2 ON t1.patient_id = t2.patient_id
                        JOIN pca.pca_prepay_account_master t3 ON t2.account_index = t3.account_index AND t3.account_type = 'O'
                        JOIN pca.pca_patient_info t4 ON t1.patient_id = t4.patient_id
                        LEFT JOIN pca.pca_prepay_refund_detail t5 ON t1.patient_id = t5.patient_id
                        LEFT JOIN hlht.lzfy_outp_balance t6 ON t1.patient_id = t6.patient_id AND t6.send_msg_count > 0
                        {whereClause}";
                    
                    totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);
                }
                
                // 使用id字段进行高效分页查询
                // 对于第一页，直接使用ROWNUM限制
                // 对于后续页面，使用id范围查询（需要前端传递lastId）
                string dataSql;
                if (pageIndex == 0)
                {
                    // 第一页：使用ROWNUM进行限制，按id降序排列（最新记录在前）
                    dataSql = $@"
                        SELECT * FROM (
                            SELECT t1.id,
                                   t4.name, 
                                   t4.id_number as IdNumber,
                                   to_char(t4.create_time, 'yyyy-mm-dd') as CreateTime,
                                   t1.balance,
                                   t3.balance as CurrentBalance,
                                   CASE
                                     WHEN t3.balance = 0 THEN '已完成退款'
                                     WHEN t3.balance < t1.balance THEN '部分退款'
                                     WHEN t3.balance > t1.balance THEN '患者余额增加'
                                     WHEN t1.balance = t3.balance THEN '未退款'
                                     ELSE '未知'
                                   END as RefundResult,
                                   t5.audit_user_name as AuditUserName,
                                   (select case
                                        when tb1.send_msg_count > 0 then
                                            decode(nvl(tb1.sendresult, '短信联系失败'),
                                                    '发送结果：success，   记录成功',
                                                    '短信已联系',
                                                    '短信联系失败')
                                        when tb1.send_msg_count = 0 then
                                            '短信未发送'
                                        end
                                    from hlht.lzfy_outp_balance tb1
                                    where tb1.patient_id = t1.patient_id) as MsgResult --短信联系结果
                            FROM pca.lzfy_prepay_refund_info t1
                            JOIN pca.pca_prepay_account_index t2 ON t1.patient_id = t2.patient_id
                            JOIN pca.pca_prepay_account_master t3 ON t2.account_index = t3.account_index AND t3.account_type = 'O'
                            JOIN pca.pca_patient_info t4 ON t1.patient_id = t4.patient_id
                            LEFT JOIN pca.pca_prepay_refund_detail t5 ON t1.patient_id = t5.patient_id
                            LEFT JOIN hlht.lzfy_outp_balance t6 ON t1.patient_id = t6.patient_id AND t6.send_msg_count > 0
                            {whereClause}
                            ORDER BY t1.id DESC
                        ) WHERE ROWNUM <= {pageSize}";
                }
                else
                {
                    // 后续页面：使用传统的OFFSET方式，但利用id字段的索引优化
                    var offset = pageIndex * pageSize;
                    dataSql = $@"
                        SELECT * FROM (
                            SELECT t1.id,
                                   t4.name, 
                                   t4.id_number as IdNumber,
                                   to_char(t4.create_time, 'yyyy-mm-dd') as CreateTime,
                                   t1.balance,
                                   t3.balance as CurrentBalance,
                                   CASE
                                     WHEN t3.balance = 0 THEN '已完成退款'
                                     WHEN t3.balance < t1.balance THEN '部分退款'
                                     WHEN t3.balance > t1.balance THEN '患者余额增加'
                                     WHEN t1.balance = t3.balance THEN '未退款'
                                     ELSE '未知'
                                   END as RefundResult,
                                   t5.audit_user_name as AuditUserName,
                                   (select case
                                            when tb1.send_msg_count > 0 then
                                                decode(nvl(tb1.sendresult, '短信联系失败'),
                                                        '发送结果：success，   记录成功',
                                                        '短信已联系',
                                                        '短信联系失败')
                                            when tb1.send_msg_count = 0 then
                                                '短信未发送'
                                        end
                                    from hlht.lzfy_outp_balance tb1
                                    where tb1.patient_id = t1.patient_id) as MsgResult, --短信联系结果
                                   ROW_NUMBER() OVER (ORDER BY t1.id DESC) as rn
                            FROM pca.lzfy_prepay_refund_info t1
                            JOIN pca.pca_prepay_account_index t2 ON t1.patient_id = t2.patient_id
                            JOIN pca.pca_prepay_account_master t3 ON t2.account_index = t3.account_index AND t3.account_type = 'O'
                            JOIN pca.pca_patient_info t4 ON t1.patient_id = t4.patient_id
                            LEFT JOIN pca.pca_prepay_refund_detail t5 ON t1.patient_id = t5.patient_id
                            LEFT JOIN hlht.lzfy_outp_balance t6 ON t1.patient_id = t6.patient_id AND t6.send_msg_count > 0
                            {whereClause}
                        ) WHERE rn > {offset} AND rn <= {offset + pageSize}";
                }
                
                var data = await connection.QueryAsync<RefundStatusData>(dataSql, parameters);
                
                _logger.LogInformation("分页查询退费情况完成，页码: {PageIndex}, 页大小: {PageSize}, 返回记录数: {Count}", 
                    pageIndex, pageSize, data.Count());
                
                return (data, totalCount);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "分页查询退费情况失败，页码: {PageIndex}, 页大小: {PageSize}", pageIndex, pageSize);
                throw;
            }
        }

        /// <summary>
        /// 获取总记录数
        /// </summary>
        public async Task<int> GetTotalCountAsync(string? name = null, string? idNumber = null, 
            string? refundResult = null, DateTime? startDate = null, DateTime? endDate = null)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var whereClause = BuildWhereClause(name, idNumber, refundResult, startDate, endDate);
                var parameters = BuildParameters(name, idNumber, refundResult, startDate, endDate);

                var countSql = $@"
                    SELECT COUNT(1) 
                    FROM pca.lzfy_prepay_refund_info t1
                    JOIN pca.pca_prepay_account_index t2 ON t1.patient_id = t2.patient_id
                    JOIN pca.pca_prepay_account_master t3 ON t2.account_index = t3.account_index AND t3.account_type = 'O'
                    JOIN pca.pca_patient_info t4 ON t1.patient_id = t4.patient_id
                    LEFT JOIN pca.pca_prepay_refund_detail t5 ON t1.patient_id = t5.patient_id
                    LEFT JOIN hlht.lzfy_outp_balance t6 ON t1.patient_id = t6.patient_id AND t6.send_msg_count > 0
                    {whereClause}";

                var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);
                
                _logger.LogInformation("获取退费情况总记录数成功: {TotalCount}", totalCount);
                
                return totalCount;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取退费情况总记录数失败");
                throw;
            }
        }

        /// <summary>
        /// 流式查询退费情况数据（用于导出，优化版本使用id字段排序）
        /// </summary>
        public async IAsyncEnumerable<RefundStatusData> GetStreamAsync(string? name = null, string? idNumber = null, 
            string? refundResult = null, DateTime? startDate = null, DateTime? endDate = null)
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var whereClause = BuildWhereClause(name, idNumber, refundResult, startDate, endDate);
            var parameters = BuildParameters(name, idNumber, refundResult, startDate, endDate);
            
            var sql = $@"
                SELECT 
                    t1.id,
                    t4.name,
                    t4.id_number as IdNumber,
                    to_char(t4.create_time, 'yyyy-mm-dd') as CreateTime,
                    t1.balance,
                    t3.balance as CurrentBalance,
                    CASE
                        WHEN t3.balance = 0 THEN '已完成退款'
                        WHEN t3.balance < t1.balance THEN '部分退款'
                        WHEN t3.balance > t1.balance THEN '患者余额增加'
                        WHEN t1.balance = t3.balance THEN '未退款'
                        ELSE '未知'
                    END as RefundResult,
                    t5.audit_user_name as AuditUserName,
                    (select case
                            when tb1.send_msg_count > 0 then
                                decode(nvl(tb1.sendresult, '短信联系失败'),
                                        '发送结果：success，   记录成功',
                                        '短信已联系',
                                        '短信联系失败')
                            when tb1.send_msg_count = 0 then
                                '短信未发送'
                        end
                    from hlht.lzfy_outp_balance tb1
                    where tb1.patient_id = t1.patient_id) as MsgResult --短信联系结果
                FROM pca.lzfy_prepay_refund_info t1
                JOIN pca.pca_prepay_account_index t2 ON t1.patient_id = t2.patient_id
                JOIN pca.pca_prepay_account_master t3 ON t2.account_index = t3.account_index AND t3.account_type = 'O'
                JOIN pca.pca_patient_info t4 ON t1.patient_id = t4.patient_id
                LEFT JOIN pca.pca_prepay_refund_detail t5 ON t1.patient_id = t5.patient_id
                LEFT JOIN hlht.lzfy_outp_balance t6 ON t1.patient_id = t6.patient_id AND t6.send_msg_count > 0
                {whereClause}
                ORDER BY t1.id DESC";
            
            var results = await connection.QueryAsync<RefundStatusData>(sql, parameters);
            foreach (var item in results)
            {
                yield return item;
            }
        }

        /// <summary>
        /// 构建WHERE子句
        /// </summary>
        private string BuildWhereClause(string? name = null, string? idNumber = null, 
            string? refundResult = null, DateTime? startDate = null, DateTime? endDate = null)
        {
            var conditions = new List<string>();

            if (!string.IsNullOrWhiteSpace(name))
                conditions.Add("t4.name LIKE :Name");
            
            if (!string.IsNullOrWhiteSpace(idNumber))
                conditions.Add("t4.id_number LIKE :IdNumber");
            
            if (!string.IsNullOrWhiteSpace(refundResult))
                conditions.Add(GetRefundResultCondition(refundResult));
            
            if (startDate.HasValue)
                conditions.Add("t4.create_time >= :StartDate");
            
            if (endDate.HasValue)
                conditions.Add("t4.create_time <= :EndDate");

            return conditions.Count > 0 ? "WHERE " + string.Join(" AND ", conditions) : "";
        }

        /// <summary>
        /// 构建查询参数
        /// </summary>
        private DynamicParameters BuildParameters(string? name = null, string? idNumber = null, 
            string? refundResult = null, DateTime? startDate = null, DateTime? endDate = null)
        {
            var parameters = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(name))
                parameters.Add("Name", $"%{name}%");
            
            if (!string.IsNullOrWhiteSpace(idNumber))
                parameters.Add("IdNumber", $"%{idNumber}%");
            
            if (startDate.HasValue)
                parameters.Add("StartDate", startDate.Value);
            
            if (endDate.HasValue)
                parameters.Add("EndDate", endDate.Value);

            return parameters;
        }

        /// <summary>
        /// 获取退款结果条件
        /// </summary>
        private string GetRefundResultCondition(string refundResult)
        {
            return refundResult switch
            {
                "已完成退款" => "t3.balance = 0",
                "部分退款" => "t3.balance < t1.balance AND t3.balance > 0",
                "患者余额增加" => "t3.balance > t1.balance",
                "未退款" => "t1.balance = t3.balance",
                _ => "1=1"
            };
        }
    }
}